﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Net;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindowsFormsApplication1.Common;
using WindowsFormsApplication1.Model;
using WindowsFormsApplication1.Model.PoiModel;
using FanjiaHotelPlatform.utils.Kits;
using Maticsoft.DBUtility;
using Newtonsoft.Json;

namespace WindowsFormsApplication1
{
    public partial class Form5 : Form
    {
        public Form5()
        {
            InitializeComponent();
        }

        public string RequestGaoDeApiSingle(string address)
        {
            string apiUrl = "http://restapi.amap.com/v3/geocode/geo";
            string apiKey = "560fa087445311bd9b55beb3202c7533"; //
            string output = "json";

            IDictionary<string, string> param = new Dictionary<string, string>();
            param.Add("key", apiKey);
            param.Add("output", output);

            if (!string.IsNullOrEmpty(address))
                param.Add("address", address);
            string result = string.Empty;
            try
            {
                //以 Get 形式请求 Api 地址
                result = HttpUtils.DoGet(apiUrl, param);
            }
            catch (Exception ex)
            {
                throw  ex;//_logger.InfoFormat($"===***********{address}获取出错：{ex.Message}");
                result = null;
            }
            return result;
        }

        private void button1_Click(object sender, EventArgs e)
        {
            var rst = RequestGaoDeApiSingle(this.textBox1.Text.Trim());

            MessageBox.Show(rst);

            //CityCenterGeo();
            return;
            //var d = Convert.ToInt32(DateTime.Now.DayOfWeek);
            //MessageBox.Show(d.ToString());

            //List<int> ls = new List<int>();
            //ls.Add(2);
            //ls.Add(7);
            //ls.Add(3);
            //ls.Add(1);
            //ls.Sort();
            //var m = string.Join(",", ls.ToArray());
            //string tmStr = string.Empty;
            //string url =
            //    "https://apis.map.qq.com/ws/place/v1/suggestion/?region=北京&keyword=美食&&region_fix=1&policy=10&key=VMDBZ-MBCW2-5Q7UU-CV7BM-CEPMT-KRBCQ";

            //var webUtil = new WebUtils();
            //var rst = webUtil.DoGet(url);

            //var r = JsonConvert.DeserializeObject<TxMapGuideSuggestResultDto>(rst);

            //var m = tmStr ?? "aaa";

            DataTable dt = DbHelperSQL.Query("SELECT * FROM HotelPosFliggyFilter_New WHERE [FjCategName] IN('地铁线路','景点','车站机场') AND FzFiNmae='磨碟沙'").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow dr in dt.Rows)
                {
                    var s = RequestGaoDeApiSinglePOI(dr["FzFiLoc"].ToString(), dr["FjCityName"].ToString(),
                        dr["FzFiNmae"].ToString());

                    dr["MapAddress"] = s;
                }

                //DbHelperSQL.SaveListDataBat(dt, "HotelPosFliggyFilter_New_Addr", true);
            }

            MessageBox.Show("OK");
        }

        private void button2_Click(object sender, EventArgs e)
        {
            DataTable dt = DbHelperSQL.Query("SELECT * FROM FliggyFilter_TW").Tables[0];

            if (dt != null && dt.Rows.Count > 0)
            {
                DataColumn dc = null;
                dc = dt.Columns.Add("OrderNum", Type.GetType("System.Int32"));
                //dc.AutoIncrement = true;//自动增加 
                //dc.AutoIncrementSeed = 1;//起始为1 
                //dc.AutoIncrementStep = 1;//步长为1 
                //dc.AllowDBNull = false;
                dt.Columns.Add("FjCityCode", Type.GetType("System.String"));
                dt.Columns.Add("FjCityName", Type.GetType("System.String"));
                dt.Columns.Add("FjCategCode", Type.GetType("System.String"));
                dt.Columns.Add("FjCategName", Type.GetType("System.String"));
                dt.Columns.Add("Lat", Type.GetType("System.String"));
                dt.Columns.Add("Lng", Type.GetType("System.String"));

                int i = 0;
                foreach (DataRow dr in dt.Rows)
                {
                    try
                    {
                        i++;

                        int idx = Convert.ToInt32(dr["FzFiIndex"]);
                        var idxName = dr["FzFiIndexName"].ToString();
                        string[] names = idxName.Split(',');
                        var n = names[idx - 1];

                        dr["FjCategName"] = n;
                        //DbHelperSQL.ExecuteSql($"UPDATE FliggyFilter_New_Bak SET FjCategName='{n}' WHERE Id='{dr["Id"]}'");
                        var locationStr = dr["FzFiLoc"].ToString();
                        string[] locs = locationStr.Split(',');
                        if (locs != null && locs.Length > 1)
                        {
                            dr["Lat"] = locs[0];
                            dr["Lng"] = locs[1];
                        }
                        dr["OrderNum"] = i;
                    }
                    catch (Exception exception)
                    {
                        MessageBox.Show("Error:" + exception.Message);
                        Console.WriteLine(exception);
                        throw;
                    }
                }

                DbHelperSQL.SaveListDataBat(dt, "FliggyFilter_TW_Bak");

                MessageBox.Show("OK!");
            }
        }

        public void CityCenterGeo()
        {
            DataTable dtG = DbHelperSQL.Query("SELECT * FROM CityCenterGeo WHERE FanjiaCityCode IS NOT NULL").Tables[0];
            if (dtG != null && dtG.Rows.Count > 0)
            {
                foreach (DataRow dr in dtG.Rows)
                {
                    try
                    {
                        var s = dr["Geo"].ToString().Split(',');
                        var r = GeoHelper.TransformBD09ToGCJ02(double.Parse(s[0]), double.Parse(s[1]));
                        DbHelperSQL.ExecuteSql($"UPDATE CityCenterGeo SET Longitude='{r.Item1}',Latitude='{r.Item2}' WHERE Id='{dr["Id"]}'");
                    }
                    catch (Exception e)
                    {
                        Console.WriteLine(e);
                    }
                }
            }
        }

        public string RequestGaoDeApiSinglePOI(string location,string city,string name)
        {
            string apiUrl = $"https://restapi.amap.com/v3/geocode/regeo?key=560fa087445311bd9b55beb3202c7533&poitype=&radius=&extensions=base&batch=false&roadlevel=0&location={location}";
            //string apiKey = "560fa087445311bd9b55beb3202c7533"; //
            //string output = "json";

            string result = string.Empty;

            var webUtil = new WebUtils();
            try
            {
                //以 Get 形式请求 Api 地址
                var jsonStr = webUtil.DoGet(apiUrl);

                var retObj = JsonConvert.DeserializeObject<FzPoiGeoModel>(jsonStr);
                result = retObj.Regeocode.FormattedAddress;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"===**********{city}:{city}，{name},[{location}]获取出错：{ex.Message}");
                //_logger.InfoFormat($"===**********{city}:{types}，{keywords}获取出错：{ex.Message}");
                result = null;
            }
            return result;
        }

        private void button3_Click(object sender, EventArgs e)
        {
            QueryTest();
            return;
            //DealFzData();
            MessageBox.Show("OK");

            return;
            string s = "里";
            MessageBox.Show(s.Length.ToString());
        }

        private void DealFzData()
        {
            DataTable dt = DbHelperMySQL.Query("SELECT d.*,c.Code FROM fz_datas d left join cities c on d.f1=c.Name WHERE f4 in('地铁线路','景点','车站机场')", "MysqlConn").Tables[0];

            DataTable dtSave = DbHelperSQL.Query("SELECT * FROM HotelFilterPositions_FzNew WHERE 1=0").Tables[0];

            int i = 0;
            foreach (DataRow dr in dt.Rows)
            {
                try
                {
                    i++;

                    DataRow drNew = dtSave.NewRow();
                    drNew["Id"] = Guid.NewGuid();
                    drNew["CityId"] = Guid.Empty;
                    drNew["CityCode"] = dr["Code"];
                    drNew["Category"] = GetCategory(dr["f4"].ToString());
                    drNew["CategoryId"] = GetCategoryId(dr["f4"].ToString()); 
                    drNew["CategoryCode"] = GetCategoryCode(dr["f4"].ToString());
                    drNew["SubCategory"] = dr["f5"];
                    drNew["OriId"] = 0;
                    drNew["Name"] = dr["f3"];                   
                    drNew["OrderNum"] = i;
                    drNew["CreationUserId"] = Guid.Empty;
                    drNew["CreationUserName"] = DBNull.Value;
                    drNew["CreationTime"] = DateTime.Now;
                    drNew["LastModificationUserId"] = DBNull.Value;
                    drNew["LastModificationUserName"] = DBNull.Value;
                    drNew["LastModificationTime"] = DBNull.Value;

                    drNew["IsDeleted"] = 0;
                    drNew["DeleterUserId"] = DBNull.Value;
                    drNew["DeleterUserName"] = DBNull.Value;
                    drNew["DeletionTime"] = DBNull.Value;
                    drNew["Status"] = 1;

                    drNew["Lng"] = string.Empty;
                    drNew["Lat"] = string.Empty;

                    var locationStr = dr["f2"].ToString();
                    string[] locs = locationStr.Split(',');
                    if (locs != null && locs.Length > 1)
                    {
                        drNew["Lng"] = locs[0];
                        drNew["Lat"] = locs[1];
                    }

                    dtSave.Rows.Add(drNew);
                }
                catch (Exception exception)
                {
                    MessageBox.Show("Error:" + exception.Message);
                    Console.WriteLine(exception);
                    throw;
                }
            }

            DbHelperSQL.ExecuteSql("TRUNCATE TABLE HotelFilterPositions_FzNew");
            DbHelperSQL.SaveListDataBat(dtSave, "HotelFilterPositions_FzNew");

            /*
             --地铁\景点\车站机场 数据
	TRUNCATE TABLE [HotelPosSearchTip_FZ]
	INSERT INTO [dbo].[HotelPosSearchTip_FZ]
           ([Id]
           ,[PosKey]
           ,[PosDisText]
           ,[CategCode]
           ,[CategName]
           ,[CategOrder]
           ,[CityCode]
           ,[CityName]
           ,[Lat]
           ,[Lng]
           ,[HotelId]		   
           ,[OrderNum]
		   ,[HotelStarRating]
           ,[CreationTime]
           ,[Status])
     SELECT
           NEWID()
           ,s.Name
           --,s.Name+ isnull(s.SubCategory,'')+','+c.Name +','+p.Name
		   ,(CASE s.Category WHEN '地铁' THEN s.Name+ ','+isnull(s.SubCategory,'')+','+c.Name +','+p.Name ELSE s.Name +','+c.Name +','+p.Name END)
           ,s.CategoryCode
           ,s.Category
           ,(CASE s.Category WHEN '景点' THEN 3 WHEN '商圈' THEN 4 WHEN '机场/火车站' THEN 5 WHEN '地铁' THEN 6 END)
           ,c.Code
           ,c.Name
           ,s.Lat
           ,s.Lng
		   ,NULL
           ,s.OrderNum
		   ,0
		   ,GETDATE()
		   ,1
	FROM [dbo].HotelFilterPositions_FzNew s,[dbo].[Cities] c,[dbo].[Provinces] p WHERE s.CityCode=c.code AND c.ProvinceId=p.Id


            --景点，商圈，机场车站数据生成
	TRUNCATE TABLE [HotelPositionTip_FZ]
	INSERT INTO [dbo].[HotelPositionTip_FZ]
           ([Id]
           ,[PosKey]
           ,[PosDisText]
           ,[CategCode]
           ,[CategName]
           ,[CategOrder]
           ,[CityCode]
           ,[CityName]
           ,[Lat]
           ,[Lng]
           ,[OrderNum]
		   ,[CreationTime]
		   ,[Status])
     SELECT
           NEWID()
           ,s.Name
           ,s.Name+','+c.Name +','+p.Name
           ,s.CategoryCode
           ,s.Category
           ,(CASE s.Category WHEN '景点' THEN 3 WHEN '商圈' THEN 4 WHEN '机场/火车站' THEN 5 END)
           ,c.Code
           ,c.Name
           ,s.Lat
           ,s.Lng
           ,s.OrderNum
		   ,GETDATE()
		   ,1
	FROM [dbo].HotelFilterPositions_FzNew s,[dbo].[Cities] c,[dbo].[Provinces] p WHERE s.CategoryId IN(1,3,4) AND s.CityCode=c.code AND c.ProvinceId=p.Id


 DELETE FROM [FanjiaHotelPlatform].[dbo].HotelFilterPositions WHERE Category IN('景点','机场/火车站','机场/车站','车站机场','地铁','地铁线路')
 INSERT INTO [FanjiaHotelPlatform].[dbo].HotelFilterPositions SELECT * FROM [FanjiaHotelPlatform].[dbo].[HotelFilterPositions_FzNew]

 DELETE FROM [FanjiaHotelPlatform].[dbo].[HotelPosSearchTip] WHERE CategName IN('景点','机场/火车站','地铁')
 INSERT INTO [FanjiaHotelPlatform].[dbo].[HotelPosSearchTip] SELECT *,NULL FROM [FanjiaHotelPlatform].[dbo].[HotelPosSearchTip_FZ]

 DELETE FROM [FanjiaHotelPlatform].[dbo].[HotelPositionTip] WHERE CategName IN('景点','机场/火车站')
 INSERT INTO [FanjiaHotelPlatform].[dbo].[HotelPositionTip] SELECT * FROM [FanjiaHotelPlatform].[dbo].[HotelPositionTip_FZ]
             */

            MessageBox.Show("OK!");
        }

        private string GetCategory(string c)
        {
            if (c == "地铁线路")
            {
                return "地铁";
            }
            else if(c == "车站机场")
            {
                return "机场/火车站";
            }

            return c;
        }

        private string GetCategoryCode(string c)
        {
            if (c == "景点")
            {
                return "spot";
            }
            else if(c == "车站机场")
            {
                return "station";
            }
            else if (c == "地铁线路")
            {
                return "subway";
            }

            return c;
        }

        private int GetCategoryId(string c)
        {
            if (c == "景点")
            {
                return 1;
            }
            else if (c == "车站机场")
            {
                return 4;
            }
            else if (c == "地铁线路")
            {
                return 2;
            }

            return 0;
        }

        public void QueryTest()
        {
            try
            {
                var t1 = Task.Run(() =>
                {
                    return DbHelperSQL.Query("SELECT * FROM Cities");
                });

                var t2 = Task.Run(() =>
                {
                    return DbHelperSQL.Query("SELECT * FROM Cities");
                });

                Task.WaitAll(t1, t2);

                MessageBox.Show(t1.Result.Tables[0].Rows.Count.ToString());
                MessageBox.Show(t2.Result.Tables[0].Rows.Count.ToString());
            }
            catch (Exception ex)
            {
                MessageBox.Show(ex.Message);
                throw;
            }            
        }
    }
}
